package com.trkj.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import com.trkj.pojo.Contract;

public interface ContractDAO extends JpaRepository<Contract, Integer>,JpaSpecificationExecutor<Contract>{
	
	@Query(nativeQuery = true, value ="SELECT emp.emp_id,emp.emp_name,dept.dept_name,contract.contract_state,contract.contract_id,emp.emp_state from emp LEFT JOIN dept on dept.dept_id=emp.dept_id\r\n" + 
			" LEFT JOIN contract on contract.emp_id=emp.emp_id \r\n" + 
			" WHERE contract.contract_state=?1 OR contract.contract_state=?2")
	public List<Object[]> findRenewContract(String state1,String state2);
	
	@Query(nativeQuery = true, value ="SELECT emp.emp_id,emp.emp_name,dept.dept_name,contract.contract_state,contract.contract_id,emp.emp_state from emp LEFT JOIN dept on dept.dept_id=emp.dept_id\r\n" + 
			" LEFT JOIN contract on contract.emp_id=emp.emp_id \r\n" + 
			" WHERE contract.contract_state=?1 OR contract.contract_state=?2  HAVING emp.emp_name LIKE ?3 or dept.dept_name LIKE ?4")
	public List<Object[]> findRenewContractORtiaojian(String state1,String state2,String empName,String deptName);

	@Query(nativeQuery = true, value ="SELECT emp.emp_id,emp.emp_name,dept.dept_name,contract.contract_state,contract.contract_id,emp.emp_state from emp LEFT JOIN dept on dept.dept_id=emp.dept_id\r\n" + 
			" LEFT JOIN contract on contract.emp_id=emp.emp_id \r\n" + 
			" WHERE contract.contract_state=?1 OR contract.contract_state=?2  HAVING emp.emp_name LIKE ?3 AND dept.dept_name LIKE ?4")
	public List<Object[]> findRenewContractANDtiaojian(String state1,String state2,String empName,String deptName);

	@Query(nativeQuery = true, value ="SELECT emp.emp_id,emp.emp_name,dept.dept_name,contract.contract_state,contract.contract_id,emp.emp_state from emp LEFT JOIN dept on dept.dept_id=emp.dept_id\r\n" + 
			" LEFT JOIN contract on contract.emp_id=emp.emp_id \r\n" + 
			" WHERE contract.contract_state=?1")
	public List<Object[]> findRenewContractYX(String state1);
	
	@Query(nativeQuery = true, value ="SELECT emp.emp_id,emp.emp_name,dept.dept_name,contract.contract_state,contract.contract_id,emp.emp_state from emp LEFT JOIN dept on dept.dept_id=emp.dept_id\r\n" + 
			" LEFT JOIN contract on contract.emp_id=emp.emp_id \r\n" + 
			" WHERE contract.contract_state=?1 HAVING emp.emp_name LIKE ?2 or dept.dept_name LIKE ?3")
	public List<Object[]> findRenewContractORtiaojianYx(String state1,String empName,String deptName);

	@Query(nativeQuery = true, value ="SELECT emp.emp_id,emp.emp_name,dept.dept_name,contract.contract_state,contract.contract_id,emp.emp_state from emp LEFT JOIN dept on dept.dept_id=emp.dept_id\r\n" + 
			" LEFT JOIN contract on contract.emp_id=emp.emp_id \r\n" + 
			" WHERE contract.contract_state=?1 HAVING emp.emp_name LIKE ?2 AND dept.dept_name LIKE ?3")
	public List<Object[]> findRenewContractANDtiaojianYx(String state1,String empName,String deptName);
	
	
	@Query(nativeQuery = true, value="SELECT contract.contract_trialtime FROM contract WHERE contract.emp_id=?")
	public String findTime(Integer empId) ;
}
